package leaveSystem.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import leaveSystem.dao.Compensation_TrackingDao;
import leaveSystem.dao.DAOException;
import leaveSystem.dao.DAOFactory;
import leaveSystem.model.CompensationTracking;
import leaveSystem.model.Employee;

import com.mysql.jdbc.Statement;

 


public class Compensation_TrackingDaoImpl implements Compensation_TrackingDao
{
    public Compensation_TrackingDaoImpl(){
    	
    }
  
    public CompensationTracking createValueObject() 
    {
          return new CompensationTracking();
    }
    
    public List <CompensationTracking> loadall(Connection conn) throws SQLException 
    {
    	 String sql = "SELECT * FROM Compensation_Tracking ORDER BY employeeid ASC ";
         List <CompensationTracking>searchResults = listQuery(conn, conn.prepareStatement(sql));
		return  searchResults;
	}         

   //>>>>>>>>>>>>>>>>>>>>>>>>       
    public  void createEmployeeLeaveMap(Connection conn, Employee e ,CompensationTracking valueObject){
    	String sql = "";
        Statement stmt = null;
        ResultSet result = null;
       // DBManagement dbmanagement = new DBManagement();
        //Connection con = dbmanagement.getConnection();
        
       
        
        try {
        	 stmt = (Statement)conn.createStatement();
        	 sql = "INSERT INTO employeeleavemap (empID ) Values ('"+1+"');";
        	 stmt.executeUpdate(sql);
        	
        }catch(Exception ex){
        	
        	
        }
        
    }
    
    //>>>>>>>>>>>>>>>>>>>>>>>>    
    public  ArrayList<CompensationTracking> LoadLeaveHistory(Connection conn, Employee e){
    	String sql = "";
        Statement stmt = null;
        ResultSet result = null;
        //DBManagement dbmanagement = new DBManagement();
        //Connection con = dbmanagement.getConnection();
        ArrayList<CompensationTracking> leaveList = new ArrayList<CompensationTracking>();
        CompensationTracking leave = new CompensationTracking();
       
        
        try {
        	 stmt = (Statement)conn.createStatement();
        	 sql = "Select * from leavecompensation where idleavecompensation in ( Select leaveID from employeeleavemap where empID = "+e.getEmployeeId()+");";
        	 result = stmt.executeQuery(sql);
        	
        	 while(result.next()){
        	 leave.setIdLeave_Compensation(String.valueOf(result.getInt("idleavecompensation")));
        	/* leave.setLeave_Start(result.getString("Leave_Start"));
        	 leave.setLeave_End(result.getString("Leave_End"));*/
        	 leave.setDate_Of_Appli(result.getString("Date_Of_Appli"));
        	 leave.setDate_Of_Approval(result.getString("Date_Of_Approval"));
        	 leave.setApprovedBy(result.getString("Approved_by"));
        	 leave.setReason(result.getString("Reason"));
        	 leave.setStatus(result.getString("Approval_Status"));
        	/* leave.setType_Of_Leave(result.getString("Type_Of_Leave"));*/
        	 leaveList.add(leave);
        	 }
        	 
        	 
        	 
        	 
        	
        }catch(Exception ex){
        	
        	
        }
        return leaveList;
    }


//>>>>>>>>>>>>>>>>>>>
    public synchronized void create(Connection conn, CompensationTracking valueObject) throws SQLException {

          String sql = "";
          Statement stmt = null;
          ResultSet result = null;
          //DBManagement dbmanagement = new DBManagement();
          //Connection con = conn.getConnection();
          Connection conn1 = (Connection) DAOFactory.getDatabaseConnection();
          
          stmt = (Statement)conn1.createStatement();

          try {
               sql = "INSERT INTO leaveapp.leavecompensation (Date_Of_Appli,"
               +" Reason, Approval_Status, Approved_by, Date_Of_Approval) VALUES ('"+
               valueObject.getDate_Of_Appli()+"','"+
              /* valueObject.getLeave_Start()+"','"+
               valueObject.getLeave_End()+"','"+
               valueObject.getType_Of_Leave()+"','"+*/
               valueObject.getReason()+"','"+
               valueObject.getStatus()+"','"+
               valueObject.getApprovedBy()+"','"+
               valueObject.getDate_Of_Approval()+"');";
               
               
               stmt.executeUpdate(sql);

             
          //     int rowcount = databaseUpdate(conn, stmt);
            /*   if (rowcount != 1) {
                    //System.out.println("PrimaryKey Error when updating DB!");
                    throw new SQLException("PrimaryKey Error when updating DB!");
               }*/

          } finally {
              if (stmt != null)
                  stmt.close();
          }


          /**
          * The following query will read the automatically generated primary key
          * back to valueObject. This must be done to make things consistent for 
          * upper layer processing logic.
          */
         /* sql = "SELECT last_insert_id()";

          try {
              stmt = conn.prepareStatement(sql);
              result = stmt.executeQuery();

              if (result.next()) {

                   valueObject.setEmployeeid((int)result.getLong(1));

              } else {
                   //System.out.println("Unable to find primary-key for created object!");
                   throw new SQLException("Unable to find primary-key for created object!");
              }
          } finally {
              if (result != null)
                  result.close();
              if (stmt != null)
                  stmt.close();
          }*/

    }


    
   /* public void save(Connection conn, CompensationTracking valueObject) 
          throws DAOException, SQLException {

          String sql = "UPDATE Compensation_Tracking SET name = ?, TotalHours = ?, RemainingDays = ?, "
               + "unusedhours = ? WHERE (employeeid = ? ) ";
          PreparedStatement stmt = null;

          try {
              stmt = conn.prepareStatement(sql);
              stmt.setString(1, valueObject.getEmployeename()); 
              stmt.setInt(2, valueObject.getTotalhours()); 
              stmt.setString(3, valueObject.getRemainingdays()); 
              stmt.setInt(4, valueObject.getUnusedhours()); 

              stmt.setInt(5, valueObject.getEmployeeid()); 

              int rowcount = databaseUpdate(conn, stmt);
              if (rowcount == 0) {
                   //System.out.println("Object could not be saved! (PrimaryKey not found)");
                   throw new DAOException("Object could not be saved! (PrimaryKey not found)");
              }
              if (rowcount > 1) {
                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
                   throw new SQLException("PrimaryKey Error when updating DB! (Many objects were affected!)");
              }
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }*/


   
  /*  public void delete(Connection conn, CompensationTracking valueObject) 
          throws DAOException, SQLException {

          String sql = "DELETE FROM Compensation_Tracking WHERE (employeeid = ? ) ";
          PreparedStatement stmt = null;

          try {
              stmt = conn.prepareStatement(sql);
              stmt.setInt(1, valueObject.getEmployeeid()); 

              int rowcount = databaseUpdate(conn, stmt);
              if (rowcount == 0) {
                   //System.out.println("Object could not be deleted (PrimaryKey not found)");
                   throw new DAOException("Object could not be deleted! (PrimaryKey not found)");
              }
              if (rowcount > 1) {
                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
                   throw new SQLException("PrimaryKey Error when updating DB! (Many objects were deleted!)");
              }
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }
*/

   
    public void deleteAll(Connection conn) throws SQLException {

          String sql = "DELETE FROM Compensation_Tracking";
          PreparedStatement stmt = null;

          try {
              stmt = conn.prepareStatement(sql);
              int rowcount = databaseUpdate(conn, stmt);
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }


    
   public int countAll(Connection conn) throws SQLException {

          // Check the cache status and use Cache if possible.
          /*if (cacheOk) {
              return cacheData.size();
          }*/


          String sql = "SELECT count(*) FROM Compensation_Tracking";
          PreparedStatement stmt = null;
          ResultSet result = null;
          int allRows = 0;

          try {
              stmt = conn.prepareStatement(sql);
              result = stmt.executeQuery();

              if (result.next())
                  allRows = result.getInt(1);
          } finally {
              if (result != null)
                  result.close();
              if (stmt != null)
                  stmt.close();
          }
          return allRows;
    }


   
    public List<CompensationTracking> searchMatching(Connection conn, CompensationTracking valueObject) throws SQLException {

          List <CompensationTracking>searchResults;

          boolean first = true;
          StringBuffer sql = new StringBuffer("SELECT * FROM Compensation_Tracking WHERE 1=1 ");

         /* if (valueObject.getEmployeeid() != 0) {
              if (first) { first = false; }
              sql.append("AND employeeid = ").append(valueObject.getEmployeeid()).append(" ");
          }

          if (valueObject.getEmployeename() != null) {
              if (first) { first = false; }
              sql.append("AND name LIKE '").append(valueObject.getEmployeename()).append("%' ");
          }

          if (valueObject.getTotalhours() != 0) {
              if (first) { first = false; }
              sql.append("AND TotalHours = ").append(valueObject.getTotalhours()).append(" ");
          }

          if (valueObject.getRemainingdays() != null) {
              if (first) { first = false; }
              sql.append("AND RemainingDays LIKE '").append(valueObject.getRemainingdays()).append("%' ");
          }

          if (valueObject.getUnusedhours() != 0) {
              if (first) { first = false; }
              sql.append("AND unusedhours = ").append(valueObject.getUnusedhours()).append(" ");
          }

*/
          sql.append("ORDER BY employeeid ASC ");

          // Prevent accidential full table results.
          // Use loadAll if all rows must be returned.
          if (first)
               searchResults = new ArrayList();
          else
               searchResults = listQuery(conn, conn.prepareStatement(sql.toString()));

          return searchResults;
    }


    /** 
     * getDaogenVersion will return information about
     * generator which created these sources.
     */
    public String getDaogenVersion() {
        return null;
    }


    /**
     * databaseUpdate-method. This method is a helper method for internal use. It will execute
     * all database handling that will change the information in tables. SELECT queries will
     * not be executed here however. The return value indicates how many rows were affected.
     * This method will also make sure that if cache is used, it will reset when data changes.
     *
     * @param conn         This method requires working database connection.
     * @param stmt         This parameter contains the SQL statement to be excuted.
     */
    protected int databaseUpdate(Connection conn, PreparedStatement stmt) throws SQLException {

          int result = stmt.executeUpdate();

        

          return result;
    }



    /**
     * databaseQuery-method. This method is a helper method for internal use. It will execute
     * all database queries that will return only one row. The resultset will be converted
     * to valueObject. If no rows were found, NotFoundException will be thrown.
     *
     * @param conn         This method requires working database connection.
     * @param stmt         This parameter contains the SQL statement to be excuted.
     * @param valueObject  Class-instance where resulting data will be stored.
     */
   protected void singleQuery(Connection conn, PreparedStatement stmt, CompensationTracking valueObject) 
          throws DAOException, SQLException {

          ResultSet result = null;

          try {
              result = stmt.executeQuery();

              if (result.next()) {

                  /* valueObject.setEmployeeid(result.getInt("employeeid")); 
                   valueObject.setEmployeename(result.getString("name")); 
                   valueObject.setTotalhours(result.getInt("TotalHours")); 
                   valueObject.setRemainingdays(result.getString("RemainingDays")); 
                   valueObject.setUnusedhours(result.getInt("unusedhours"));*/ 

              } else {
                    //System.out.println("Compensation_Tracking Object Not Found!");
                    throw new DAOException("Compensation_Tracking Object Not Found!");
              }
          } finally {
              if (result != null)
                  result.close();
              if (stmt != null)
                  stmt.close();
          }
    }


    /**
     * databaseQuery-method. This method is a helper method for internal use. It will execute
     * all database queries that will return multiple rows. The resultset will be converted
     * to the List of valueObjects. If no rows were found, an empty List will be returned.
     *
     * @param conn         This method requires working database connection.
     * @param stmt         This parameter contains the SQL statement to be excuted.
     */
   protected List<CompensationTracking> listQuery(Connection conn, PreparedStatement stmt) throws SQLException {

          ArrayList searchResults = new ArrayList();
          ResultSet result = null;

          try {
              result = stmt.executeQuery();

              while (result.next()) {
                   CompensationTracking temp = createValueObject();

                  /* temp.setEmployeeid(result.getInt("employeeid")); 
                   temp.setEmployeename(result.getString("name")); 
                   temp.setTotalhours(result.getInt("TotalHours")); 
                   temp.setRemainingdays(result.getString("RemainingDays")); 
                   temp.setUnusedhours(result.getInt("unusedhours")); */

                   searchResults.add(temp);
              }

          } finally {
              if (result != null)
                  result.close();
              if (stmt != null)
                  stmt.close();
          }

          return (List)searchResults;
    }







	@Override
	public void save(Connection conn, CompensationTracking valueObject)
			throws DAOException, SQLException {
		// TODO Auto-generated method stub
		
	}







	@Override
	public void delete(Connection conn, CompensationTracking valueObject)
			throws DAOException, SQLException {
		// TODO Auto-generated method stub
		
	}





	


}
